package info.javafans.dao;

import info.javafans.database.DatabaseManager;
import info.javafans.domain.Post;
import info.javafans.utils.DateTime;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class PostDao {
	DatabaseManager dbManager = new DatabaseManager();
	
	/**
	 * 查找所有文章
	 * @return
	 */
	public List<Post> selectAllPosts() {

		List<Post> list = new ArrayList<Post>();
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "select * from jb_posts order by date desc";
			ResultSet result = stmt.executeQuery(sql);

			while (result.next()) {
				Post post = new Post();
				
				post.setId(""+result.getInt("id"));
				post.setTitle(result.getString("title"));
				post.setContent(result.getString("content"));
				post.setAuthor(new UserDao().selectUserById(""+result.getInt("author")));
				post.setCategory(new CategoryDao().selectCategoryById(""+result.getInt("category")));
				DateTime date = DateTime.format(result.getString("date"));
				post.setDate(date);
				post.setTags(result.getString("tags"));
				post.setCommentCount(result.getInt("comment_count"));
				post.setViewCount(result.getInt("view_count"));
				
				list.add(post);
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-selectAllPosts-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
				return list;
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-selectAllPosts");
				// e.printStackTrace();
			}
		}

		return null;
	}
	
	/**
	 * 查找某一分类下所有文章
	 * @return
	 */
	public List<Post> selectPostByCategory(String id) {

		List<Post> list = new ArrayList<Post>();
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "select * from jb_posts order by date desc where category = "+id;
			ResultSet result = stmt.executeQuery(sql);

			while (result.next()) {
				Post post = new Post();
				
				post.setId(""+result.getInt("id"));
				post.setTitle(result.getString("title"));
				post.setContent(result.getString("content"));
				post.setAuthor(new UserDao().selectUserById(""+result.getInt("author")));
				post.setCategory(new CategoryDao().selectCategoryById(""+result.getInt("category")));
				DateTime date = DateTime.format(result.getString("date"));
				post.setDate(date);
				post.setTags(result.getString("tags"));
				post.setCommentCount(result.getInt("comment_count"));
				post.setViewCount(result.getInt("view_count"));

				list.add(post);
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-selectPostByCategory-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
				return list;
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-selectPostByCategory");
				// e.printStackTrace();
			}
		}

		return null;
	}
	
	/**
	 * 文章阅读排名，只标题
	 * @return
	 */
	public List<Post> selectMostViewTitle() {

		List<Post> list = new ArrayList<Post>();
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "select * from jb_posts order by view_count desc limit 0,10";
			ResultSet result = stmt.executeQuery(sql);

			while (result.next()) {
				Post post = new Post();
				
				post.setId(""+result.getInt("id"));
				post.setTitle(result.getString("title"));
				post.setContent("");//不包含文章内容
				post.setAuthor(new UserDao().selectUserById(""+result.getInt("author")));
				post.setCategory(new CategoryDao().selectCategoryById(""+result.getInt("category")));
				DateTime date = DateTime.format(result.getString("date"));
				post.setDate(date);
				post.setTags(result.getString("tags"));
				post.setCommentCount(result.getInt("comment_count"));
				post.setViewCount(result.getInt("view_count"));

				list.add(post);
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-selectMostViewTitle-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
				return list;
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-selectMostViewTitle");
				// e.printStackTrace();
			}
		}

		return null;
	}
	
	/**
	 * 按id查询文章
	 * @return
	 */
	public Post selectPostById(String id) {

		Post post = new Post();
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "select * from jb_posts where id = " + id;
			ResultSet result = stmt.executeQuery(sql);

			if (result.next()) {
				post.setId(""+result.getInt("id"));
				post.setTitle(result.getString("title"));
				post.setContent(result.getString("content"));
				post.setAuthor(new UserDao().selectUserById(""+result.getInt("author")));
				post.setCategory(new CategoryDao().selectCategoryById(""+result.getInt("category")));
				DateTime date = DateTime.format(result.getString("date"));
				post.setDate(date);
				post.setTags(result.getString("tags"));
				post.setCommentCount(result.getInt("comment_count"));
				post.setViewCount(result.getInt("view_count"));
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-selectMostView-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
				return post;
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-selectMostView");
				// e.printStackTrace();
			}
		}

		return null;
	}
	
	/**
	 * 查询最新文章，仅标题，不含内容
	 * @return
	 */
	public List<Post> selectNewPostTitle() {
			
			List<Post> list = new ArrayList<Post>();
			Connection conn = dbManager.getConnection();
			try {
				Statement stmt = conn.createStatement();
				String sql = "select * from jb_posts order by date desc limit 0,10";
				ResultSet result = stmt.executeQuery(sql);

				while (result.next()) {
					Post post = new Post();
					
					post.setId(""+result.getInt("id"));
					post.setTitle(result.getString("title"));
					post.setContent("");//不包含文章内容
					post.setAuthor(new UserDao().selectUserById(""+result.getInt("author")));
					post.setCategory(new CategoryDao().selectCategoryById(""+result.getInt("category")));
					DateTime date = DateTime.format(result.getString("date"));
					post.setDate(date);
					post.setTags(result.getString("tags"));
					post.setCommentCount(result.getInt("comment_count"));
					post.setViewCount(result.getInt("view_count"));

					list.add(post);
				}

			} catch (SQLException e) {
				System.out
						.println("info.javafans: PostDao-selectNewPostTitle-execute query");
				// e.printStackTrace();
			} finally {
				try {
					conn.close();
					return list;
				} catch (SQLException e) {
					System.out
							.println("info.javafans:  PostDao-selectNewPostTitle");
					// e.printStackTrace();
				}
			}

			return null;
	}
	
	/**
	 * 添加Post
	 * @param post
	 * @return
	 */
	public boolean addPost(Post post) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "insert into jb_posts(title,content,author,category,date,tags,comment_count,view_count) values('"+
			post.getTitle()+"','"+post.getContent()+"'," +
			post.getAuthor().getId()+","+
			post.getCategory().getId()+",'"+
			post.getDate().getFmtDateTime()+"','"+
			post.getTags()+"',"+
			post.getCommentCount()+","+
			post.getViewCount()+")";
//			System.out.println(sql);
			int result = stmt.executeUpdate(sql);
			if (result == 1) {
				return true;
			}
			else {
				return false;
			}
		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-addPost-execute query");
			// e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-addPost");
				// e.printStackTrace();
			}
		}

		return false;
	}
	
	/**
	 * 更新Post
	 * @param post
	 * @return
	 */
	public boolean updatePost(Post post) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "update jb_posts set title='"+post.getTitle()+
			"',category="+post.getCategory().getId()+
			",tags='"+post.getTags()+
			"',content='"+post.getContent()+"' where id ="+post.getId();
			int result = stmt.executeUpdate(sql);
			if (result == 1) {
				return true;
			}
			else {
				return false;
			}
		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-doUpdatePost-execute query");
			// e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-doUpdatePost");
				// e.printStackTrace();
			}
		}

		return false;
	}
	
	/**
	 * 删除Post
	 * @param post
	 * @return
	 */
	public boolean deletePostById(String id) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			String sql = "delete from jb_posts where id="+id;
			int result = stmt.executeUpdate(sql);
			if (result == 1) {
				return true;
			}
			else {
				return false;
			}
		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-deletePostById-execute query");
			// e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-deletePostById");
				// e.printStackTrace();
			}
		}

		return false;
	}
	
	/**
	 * ViewCount增加
	 */
	public boolean doViewCountUp(String id) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			Post post = this.selectPostById(id);
			String sql = "update jb_posts set view_count=" + (post.getViewCount()+1) +" where id ="+id;
			int result = stmt.executeUpdate(sql);
			
			if (result > 0) {
				return true;
			}
			else {
				return false;
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-doViewCountUp-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-doViewCountUp");
				// e.printStackTrace();
			}
		}

		return false;
	}
	
	/**
	 * ViewCount减少
	 */
	public boolean doViewCountDown(String id) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			Post post = this.selectPostById(id);
			String sql = "update jb_posts set view_count=" + (post.getViewCount()-1) +" where id ="+id;
			int result = stmt.executeUpdate(sql);
			
			if (result > 0) {
				return true;
			}
			else {
				return false;
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-doViewCountDown-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-doViewCountDown");
				// e.printStackTrace();
			}
		}

		return false;
	}
	
	/**
	 * CommentCount增加
	 */
	public boolean doCommentCountUp(String id) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			Post post = this.selectPostById(id);
			String sql = "update jb_posts set comment_count=" + (post.getCommentCount()+1) +" where id ="+id;
//			System.out.println(sql);
			int result = stmt.executeUpdate(sql);
			
			if (result > 0) {
				return true;
			}
			else {
				return false;
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-doCommentCountUp-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-doCommentCountUp");
				// e.printStackTrace();
			}
		}

		return false;
	}
	
	/**
	 * CommentCount减少
	 */
	public boolean doCommentCountDown(String id) {
		Connection conn = dbManager.getConnection();
		try {
			Statement stmt = conn.createStatement();
			Post post = this.selectPostById(id);
			String sql = "update jb_posts set comment_count=" + (post.getCommentCount()-1) +" where id ="+id;
			int result = stmt.executeUpdate(sql);
			
			if (result > 0) {
				return true;
			}
			else {
				return false;
			}

		} catch (SQLException e) {
			System.out
					.println("info.javafans: PostDao-doCommentCountDown-execute query");
			// e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out
						.println("info.javafans:  PostDao-doCommentCountDown");
				// e.printStackTrace();
			}
		}

		return false;
	}
}
